﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;


namespace Job.Core
{
    public class SYSequenceDAL
    {
        #region  直接执行Sql语句或存储过程
        public static IDataReader ExecuteDataReader(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            return db.ExecuteReader(cmd);
        }

        public static DataTable ExecuteDataTable(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            DataSet ds = db.ExecuteDataSet(cmd);
            return ds.Tables[0];
        }

        public static DataSet ExecuteDataSet(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            DataSet ds = db.ExecuteDataSet(cmd);
            return ds;
        }

        public static int ExecuteNonQuery(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            return db.ExecuteNonQuery(cmd);
        }

        public static object ExecuteScalar(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            return db.ExecuteScalar(cmd);
        }

        public static List<object> GetParametersValue(DataBaseEnum dbName, string strCommand, CommandType commandType, List<DataBaseParameter> listParam)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strCommand);
            cmd.CommandType = commandType;
            if (listParam != null)
            {
                foreach (DataBaseParameter dbPm in listParam)
                {
                    if (dbPm.OutPut)
                        db.AddOutParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.Size);
                    else
                        db.AddInParameter(cmd, dbPm.ParamName, dbPm.ParamDbType, dbPm.ParamValue);
                }
            }
            db.ExecuteNonQuery(cmd);
            List<object> objArr = new List<object>();
            foreach (DataBaseParameter dbPm in listParam)
            {
                if (dbPm.OutPut)
                    objArr.Add(cmd.Parameters[dbPm.ParamName].Value);
            }
            return objArr;
        }

        /// <summary>
        /// 获得记录数
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public static int GetCount(DataBaseEnum dbName, string where, string TableName)
        {
            string strSQL = string.Format("SELECT COUNT(0) FROM {0}", TableName);
            if (!string.IsNullOrEmpty(where))
                strSQL += string.Format(" WHERE {0}", where);
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strSQL.ToString());
            return Convert.ToInt32(db.ExecuteScalar(cmd));
        }

        #region 分页获取数据

        /// <param name="strSql">strSql句</param>
        public static DataTable GetDataTableByQuery(DataBaseEnum dbName, string strSql)
        {
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            return db.ExecuteDataSet(cmd).Tables[0];
        }

        /// <param name="strWhere">Where子句</param>
        /// <param name="PageSize">每页显示记录数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="OrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="colList">以逗号分隔的查询字段名称</param>
        /// <param name="fldOrder">排序字段</param>
        /// <param name="tblName">表名</param>
        public static DataTable GetDataTableByPage(DataBaseEnum dbName, string strWhere, int PageSize, int PageIndex, bool OrderType, string colList, string fldOrder, string tblName)
        {
            string strSQL = BuildSql(strWhere, PageSize, PageIndex, OrderType, colList, fldOrder, tblName);
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            return db.ExecuteDataSet(cmd).Tables[0];
        }

        /// <param name="strWhere">Where子句</param>
        /// <param name="PageSize">每页显示记录数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="OrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="colList">以逗号分隔的查询字段名称</param>
        /// <param name="fldOrder">排序字段</param>
        /// <param name="tblName">表名</param>
        public static IDataReader GetDataReaderByPage(DataBaseEnum dbName, string strWhere, int PageSize, int PageIndex, bool OrderType, string colList, string fldOrder, string tblName)
        {
            string strSQL = BuildSql(strWhere, PageSize, PageIndex, OrderType, colList, fldOrder, tblName);
            string connName = BaseAccessHelper.getConnName(dbName);
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            return db.ExecuteReader(cmd);
        }

        /// <param name="strWhere">组成SQL语句的Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前需要获取的页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">逗号分隔的字段名字符串</param>
        /// <param name="strOrderColumn">排序字段</param>
        /// <param name="strTableName">表名</param>
        public static string BuildSql(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn, string strTableName)
        {
            strColumnList = BaseAccessHelper.GetSQLFildList(strColumnList);
            strWhere = string.IsNullOrEmpty(strWhere) ? "" : string.Format(" WHERE {0}", strWhere);
            StringBuilder sbSql = new StringBuilder();
            string strOrder = string.Format(" ORDER BY {0} {1}", strOrderColumn, blOrderType ? "DESC" : "ASC");
            //return string.Format("SELECT {0} FROM(SELECT {0},ROW_NUMBER() OVER({1}) AS ROW FROM {2}{3}) A WHERE ROW BETWEEN {4} AND {5}", strColumnList, strOrder, strTableName, strWhere, (intPageIndex - 1) * intPageSize + 1, intPageIndex * intPageSize);
            return string.Format("SELECT * FROM(SELECT {0},ROW_NUMBER() OVER({1}) AS ROW FROM {2}{3}) A WHERE ROW BETWEEN {4} AND {5}", strColumnList, strOrder, strTableName, strWhere, (intPageIndex - 1) * intPageSize + 1, intPageIndex * intPageSize);
        }
        #endregion 分页查询
        #endregion 直接执行Sql语句或存储过程

        /*
        #region 直接执行Sql语句或存储过程 MongoDB
        public static void MongoUpdate(DataBaseEnum dbe, string strTableName, MongoDB.Driver.Document doc, MongoDB.Driver.Document docSelector)
        {
            MongoDB.Driver.Mongo mongo = new MongoDB.Driver.Mongo(BaseAccessHelper.getMongoDBConnectionString(dbe));
            mongo.Connect();
            MongoDB.Driver.Database db = mongo[dbe.ToString()];
            MongoDB.Driver.IMongoCollection collection = db[strTableName];
            collection.Update(doc, docSelector);

            mongo.Disconnect();
        }

        /// <summary>
        /// 分组统计
        /// </summary>
        /// <param name="docWhere">分组查询条件</param>
        /// <param name="strGroupCloumn">分组字段</param>
        /// <returns></returns>
        public static Dictionary<string, int> MongoGroupBy(DataBaseEnum dbe, string strTableName, MongoDB.Driver.Document docWhere, string strGroupCloumn)
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();

            MongoDB.Driver.Mongo mongo = new MongoDB.Driver.Mongo(BaseAccessHelper.getMongoDBConnectionString(dbe));
            try
            {
                mongo.Connect();
                MongoDB.Driver.Database db = mongo[dbe.ToString()];
                MongoDB.Driver.IMongoCollection collection = db[strTableName];
                string mapfunction = "function() { emit(this." + strGroupCloumn + ", 1); }";
                string reducefunction = @"function(key, current ){
                                        var count = 0;
                                        for(var i in current) {
                                            count += current[i];
                                        }
                                        return count;
                                    };";
                using (MongoDB.Driver.MapReduceBuilder mrb = collection.MapReduceBuilder().Map(mapfunction).Reduce(reducefunction).Query(docWhere))
                {
                    using (MongoDB.Driver.MapReduce mr = mrb.Execute())
                    {
                        foreach (MongoDB.Driver.Document doc in mr.Documents)
                        {
                            dict.Add(doc["_id"].ToString(), Convert.ToInt32(doc["value"]));
                        }
                    }
                }
            }
            catch
            { }
            finally
            {
                mongo.Disconnect();
            }

            return dict;
        }

        public static List<MongoDB.Driver.Document> MongoGetList(DataBaseEnum dbe, string strTableName, MongoDB.Driver.Document doc, string strNeedColumn, bool blOrderType, string strOrderColumn)
        {
            MongoDB.Driver.Mongo mongo = new MongoDB.Driver.Mongo(BaseAccessHelper.getMongoDBConnectionString(dbe));
            mongo.Connect();
            MongoDB.Driver.Database db = mongo[dbe.ToString()];
            MongoDB.Driver.IMongoCollection collection = db[strTableName];
            MongoDB.Driver.ICursor cursor;
            if (string.IsNullOrEmpty(strNeedColumn) || strNeedColumn.Trim() == "*")
                cursor = collection.Find(doc).Sort(strOrderColumn, blOrderType ? MongoDB.Driver.IndexOrder.Descending : MongoDB.Driver.IndexOrder.Ascending);
            else
            {
                MongoDB.Driver.Document docFields = new MongoDB.Driver.Document();
                string[] strArr = strNeedColumn.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                foreach (var obj in strArr)
                {
                    docFields.Append(obj, 1);
                }
                cursor = collection.Find(doc).Sort(strOrderColumn, blOrderType ? MongoDB.Driver.IndexOrder.Descending : MongoDB.Driver.IndexOrder.Ascending).Fields(docFields);
            }
            List<MongoDB.Driver.Document> listDocument = cursor.Documents.ToList<MongoDB.Driver.Document>();
            mongo.Disconnect();
            return listDocument;
        }

        public static MongoDB.Driver.Document MongoGetModel(DataBaseEnum dbe, string strTableName, MongoDB.Driver.Document doc)
        {
            MongoDB.Driver.Mongo mongo = new MongoDB.Driver.Mongo(BaseAccessHelper.getMongoDBConnectionString(dbe));
            mongo.Connect();
            MongoDB.Driver.Database db = mongo[dbe.ToString()];
            MongoDB.Driver.IMongoCollection collection = db[strTableName];
            MongoDB.Driver.Document document;
            document = collection.FindOne(doc);
            mongo.Disconnect();
            return document;
        }
        #endregion
        */

        #region 高效批量插入数据
        /// <summary>
        /// 高效批量插入数据
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="dtData">源数据表</param>
        /// <param name="strTName">插入数据表名</param>
        /// <param name="listCloumns">插入数据列名（保持源数据表与插入数据表列名一致）</param>
        /// <returns></returns>
        public static bool FastInsertData(DataBaseEnum dbName, DataTable dtData, string strTName, List<string> listCloumns, out string strException)
        {
            strException = "";
            try
            {
                SqlConnection conn = new SqlConnection(BaseAccessHelper.GetSqlConnectionString(dbName));
                conn.Open();
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
                {
                    sqlBC.BatchSize = 100000;
                    sqlBC.BulkCopyTimeout = 60;
                    sqlBC.DestinationTableName = strTName.ToLower().IndexOf("dbo.") == -1 ? ("dbo." + strTName) : strTName;
                    foreach (var obj in listCloumns)
                    {
                        sqlBC.ColumnMappings.Add(obj, obj);
                    }
                    sqlBC.WriteToServer(dtData);
                }
                conn.Dispose();
                return true;
            }
            catch(Exception ex)
            {
                strException = ex.Message;
                return false; 
            }
        }
        #endregion
    }
}
